package com.sanxiau.dormitory.liu.controller;


import com.sanxiau.dormitory.liu.dao.RichesDao;
import com.sanxiau.dormitory.liu.dao.StudentDao;
import com.sanxiau.dormitory.liu.dao.UserDao;
import com.sanxiau.dormitory.liu.entity.Riches;
import com.sanxiau.dormitory.liu.entity.Student;
import com.sanxiau.dormitory.liu.entity.User;
import com.sanxiau.dormitory.yang.util.MD5Util;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import static org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING;

/**
 * 导入学生信息
 */
@Controller
@RequestMapping("/excelInfor")
public class ExclController {

    @Autowired
    StudentDao studentDao;

    @Autowired
    UserDao userDao;

    @Autowired
    RichesDao richesDao;

    @PostMapping("/upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {

        InputStream in =null;
        try {
            //将file转InputStream
            in = file.getInputStream();

            //将InputStream转XLSX对象（即表格对象）
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(in);
            //获取表格的第一页
            XSSFSheet xssfSheet=xssfWorkbook.getSheetAt(0);
            //获取该页有多少数据
            int rowNum= xssfSheet.getLastRowNum();
            for(int i=1;i<=rowNum;i++){
                //获取当前行
                XSSFRow xssfRow=xssfSheet.getRow(i);

                //由于学号是纯数字，默认纯数字是以double传的，写进据库会变成科学计数法格式的，需要改为以string传进来
                 xssfRow.getCell(0).setCellType(CELL_TYPE_STRING);
                 xssfRow.getCell(3).setCellType(CELL_TYPE_STRING);
                 xssfRow.getCell(5).setCellType(CELL_TYPE_STRING);
                 xssfRow.getCell(14).setCellType(CELL_TYPE_STRING);

                //以此获得该行的所有单元格
                String stuId=xssfRow.getCell(0).toString();
                String stuName=xssfRow.getCell(1).toString();
                String stuSex=xssfRow.getCell(2).toString();
                String idCard=xssfRow.getCell(3).toString();
                String houId=xssfRow.getCell(4).toString();
                String dorId=xssfRow.getCell(5).toString();

                String stuEnrs=xssfRow.getCell(6).toString();
                String stuEnr = stuEnrs.substring(0,4)+"-"+stuEnrs.substring(5,7)+"-"+stuEnrs.substring(8,10);

                String stuBirths=xssfRow.getCell(7).toString();
                String stuBirth = stuBirths.substring(0,4)+"-"+stuBirths.substring(5,7)+"-"+stuBirths.substring(5,7);

                String stuStudy=xssfRow.getCell(8).toString();
                String stuClass=xssfRow.getCell(9).toString();
                String stuCname=xssfRow.getCell(10).toString();
                String stuPolit=xssfRow.getCell(11).toString();
                String stuAdd=xssfRow.getCell(12).toString();
                String email=xssfRow.getCell(13).toString();
                String stuTel=xssfRow.getCell(14).toString();

                Student student = new Student();

                //将数据写入实体类
                student.setStuId(stuId);
                student.setStuName(stuName);
                student.setStuSex(stuSex);
                student.setIdCard(idCard);
                student.setHouId(houId);
                student.setDorId(dorId);
                student.setStuEnr(stuEnr);
                student.setStuBirth(stuBirth);
                student.setStuStudy(stuStudy);
                student.setStuClass(stuClass);
                student.setStuCname(stuCname);
                student.setStuPolit(stuPolit);
                student.setStuAdd(stuAdd);
                student.setEmail(email);
                student.setStuTel(stuTel);
                //增加学生
                student.setStuSta("在校");
                //将实体类插入数据库
                studentDao.save(student);

                //增加学生所住寝室信息
                Riches riches = new Riches();
                riches.setHouId(houId);
                riches.setDorId(dorId);
                riches.setStuName(stuName);
                riches.setStuId(stuId);
                richesDao.save(riches);

                //添加到登录表
                User user = new User();
                user.setUserName(student.getStuId());
                //身份证后六位加密
                int pd = student.getIdCard().length();
                String pwd = student.getIdCard().substring(pd-6, pd);
                user.setUserPwd(MD5Util.getPasswordByMD5(pwd, student.getStuId()));
                user.setUserName(student.getStuId());
                user.setTrueName(student.getStuName());
                user.setLinkTel(student.getStuTel());
                user.setEmail(student.getEmail());
                user.setLinkAddre(student.getStuAdd());
                user.setState("2");
                user.setIdCard(student.getIdCard());
                user.setLoginState("0");
                user.setRemark("学生");
                userDao.save(user);

            }
        } catch (Exception e) {
            e.printStackTrace();
            return "系统繁忙！";
        }

        return "文件上传成功！";
    }

}
